/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package csse3004.GD.DA;

import csse3004.GD.PAdmin.AssignPollCreatorData;
import csse3004.GD.PAdmin.CreatorList;
import csse3004.GD.PAdmin.HomeData;
import csse3004.GD.PAdmin.MasterList;
import csse3004.GD.PAdmin.PollAdminService;
import csse3004.GD.PAdmin.PollReport;
import csse3004.GD.PAdmin.pollData;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.logging.Logger;

/**
 *
 * @author Tom McNair - S41758984
 */
public class PollAdminDA extends GenericDA implements PollAdminService {

    private static final Logger m_logger =
            Logger.getLogger(LoginDA.class.getName());
    
    /**
     * Function to retrieve the polls defined by the specified Poll Administrator
     * 
     * @param userID ID of the Poll Master
     * @return 
     */
    public ArrayList<HomeData> getHomeData(int userID) {
        String sql1 = "SELECT * FROM Poll WHERE Admin_ID = " + userID;
        String sql2;
        String sql3;
        
        ArrayList<HomeData> homeData = new ArrayList<HomeData>();
        HomeData dataItem;
        ArrayList<Integer> creatorIDs;
        ArrayList<String> creatorNames;
               
        m_logger.info(sql1);

        try {
            ResultSet results = DatabaseQuery(sql1);

            while (results.next()) {
                dataItem = new HomeData();
                dataItem.setID(results.getInt("Poll_ID"));
                dataItem.setPollName(results.getString("Poll_Name"));
                sql2 = "SELECT Users.User_ID, Users.Username FROM Users, Poll_Creator WHERE Users.User_ID = Poll_Creator.Creator_ID AND Poll_Creator.Poll_ID = "+results.getInt("Poll_ID");
                m_logger.info(sql2);
                ResultSet creatorResults = DatabaseQuery(sql2);
                creatorIDs = new ArrayList<Integer>();
                creatorNames = new ArrayList<String>();
                int m = 0;
                while (creatorResults.next()) {
                    m_logger.info("iteration: "+m); 
                    int creatorID = creatorResults.getInt("User_ID");
                    creatorIDs.add(creatorID);
                     creatorNames.add(creatorResults.getString("Username"));
                    }
                m_logger.info("working");
                dataItem.setPollCreatorID(creatorIDs);
                dataItem.setCreatorName(creatorNames);
                homeData.add(dataItem);
            }
            return homeData;

        } catch (Exception ex) {
            m_logger.info("getHomeData() failed\n");
            m_logger.info(ex.toString());
        }
        return null;
    }

    /** Function to define a new poll
     *
     * @param pollName
     * @param adminID
     */
    public boolean createPoll(String pollName, int adminID) {
        int pollID = getNextID("Poll");
        long time = new java.util.Date().getTime();
        String sql = "INSERT INTO poll(Poll_ID, Poll_Name, Public_Result, Anonymous, Admin_ID, Creation_Date) VALUES(" + pollID + ",'" + pollName + "', 'No', 'Yes'," + adminID + ", "+time+")";

        try {
            if (DatabaseInsert(sql)) {
                return true;
            } else {
                return false;
            }
        } catch (Exception ex) {
            m_logger.info("createPoll() failed\n");
            m_logger.info(ex.toString());
            return false;
        }
    }

    /** Function to assign a poll to a poll creator
     *
     * @param pollID
     * @param creatorID
     */
    public boolean assignToPollCreator(int pollID, int creatorID) {
        m_logger.info("Poll ID= "+pollID+"\n");
        m_logger.info("Creator ID = "+creatorID+"\n");
        String sql = "INSERT INTO Poll_Creator VALUES (" + pollID + "," + creatorID + ")";

        try {
            if (DatabaseInsert(sql)) {
                return true;
            } else {
                return false;
            }
        } catch (Exception ex) {
            m_logger.info("createPoll() failed\n");
            m_logger.info(ex.toString());
            return false;
        }
    }
    
    public boolean removePollCreator(int pollID, int creatorID) {
        String sql = "DELETE FROM Poll_Creator WHERE POLL_ID = " + pollID + " AND Creator_ID =" + creatorID;
        
        try {
            if (DatabaseInsert(sql)) {
                return true;
            } else {
                return false;
            }
        } catch (Exception ex) {
            m_logger.info("removePollCreator() failed\n");
            m_logger.info(ex.toString());
            return false;
        }
    }
    
    public ArrayList<AssignPollCreatorData> getUnassignedCreators(int pollID) {
        String sql = "SELECT * FROM Users WHERE Type = 'Creator' AND User_ID NOT IN "
                + "(SELECT Creator_ID FROM Poll_Creator where POLL_ID = "+pollID+")";
        ArrayList<AssignPollCreatorData> creatorIDs = new ArrayList<AssignPollCreatorData>();
        AssignPollCreatorData creator;// = new AssignPollCreatorData();
        
        try {
            ResultSet results = DatabaseQuery(sql);
            
            while (results.next()) {
                creator = new AssignPollCreatorData();
                int userID = results.getInt("User_ID");
                creator.setPollCreatorID(userID);
                creator.setCreatorName(results.getString("Username"));
                creator.setEmail(results.getString("Email"));
                
//                String sql2 = "SELECT Location.Name FROM Location, User_Location WHERE Location.Location_ID = User_Location.Location_ID AND User_Location.User_ID = "+userID;
//                ResultSet results2 = DatabaseQuery(sql2);
//                if (results2.next()) {
//                    creator.setLocation(results2.getString("Name"));
//                }
//                
//                String sql3 = "SELECT Organisation.Name FROM Organisation, User_Organisation WHERE Organisation.Organisation_ID = User_Organisation.Organisation_ID AND User_Organisation.User_ID = "+userID;
//                results2 = DatabaseQuery(sql3);
//                if (results2.next()) {
//                   creator.setLocation(results2.getString("Name"));
//               }
                creatorIDs.add(creator);
            }
        } catch (Exception ex) {
            m_logger.info("getUnassignedCreators() failed\n");
            m_logger.info(ex.toString());
            return null;
        }
        return creatorIDs;
    }
    
    public PollReport getPollReport() {
        PollReport report = new PollReport();
        pollData data;
        ArrayList<pollData> dataList = new ArrayList<pollData>();
        MasterList masters;
        ArrayList<MasterList> masterList = new ArrayList<MasterList>();
        CreatorList creators;
        ArrayList<CreatorList> creatorList = new ArrayList<CreatorList>();
        
        String sql1 = "SELECT Poll_ID, Poll_Name FROM Poll";  // removed Open_Time
        String sql2 = "SELECT Users.User_ID, Users.Username, Poll_Master.Poll_ID FROM Users, Poll_Master WHERE Users.User_ID = Poll_Master.Master_ID";
        String sql3 = "SELECT Users.User_ID, Users.Username, Poll_Creator.Poll_ID FROM Users, Poll_Creator WHERE Users.User_ID = Poll_Creator.Creator_ID";
        
        try {
            ResultSet results = DatabaseQuery(sql1);
            while (results.next()) {
                data = new pollData();
                data.setUserID(results.getInt("Poll_ID"));
                data.setName(results.getString("Poll_Name"));
                
                //data.setDate(null);
                //data.setDate(results.getDate("Open_Time"));   // parameter should reflect the new "date" attribute
                dataList.add(data);
            }
            results = DatabaseQuery(sql2);
            while (results.next()) {
                masters = new MasterList();
                masters.setUserID(results.getInt("User_ID"));
                masters.setUsername(results.getString("Username"));
                //masters.addAssignedPoll(results.getInt("Poll_ID"));
                masterList.add(masters);
            }
            results = DatabaseQuery(sql3);
            while (results.next()) {
                creators = new CreatorList();
                creators.setUserID(results.getInt("User_ID"));
                creators.setUsername(results.getString("Username"));
                creators.setAssignedPoll(results.getInt("Poll_ID"));
                creatorList.add(creators);
            }
            report.setPollCreators(creatorList);
            report.setPollMasters(masterList);
            report.setPolls(dataList);
            return report;
        } catch (Exception Ex) {
            m_logger.info("getPollReport() failed");
            m_logger.info(Ex.toString());
            return null;
        }
    }
}
